"""
Case Type   : GSC功能模块
Case Name   : 二级分区表表结构变更，gs_gsc_dbstat_info函数gsc缓存功能验证
Create At   : 2024/01/19
Owner       : @peilinqian
Description :
    step1:打开gsc开关;
    step2:重启数据库生效;
    step3:创建数据库;
    step4:创建表插入数据，并查询;
    step5:查询表;
    step5.1:查看gs_gsc_dbstat_info函数gsc缓存;
    step6:增加索引;
    step6.1:查询表;
    step6.2:查看gs_gsc_dbstat_info函数gsc缓存;
    step7:修改分区表，添加二级分区;
    step7.1:查询表;
    step7.2:查看gs_gsc_dbstat_info函数gsc缓存;
    step8:修改分区表，添加一级分区;
    step8.1:查询表;
    step8.2:查看gs_gsc_dbstat_info函数gsc缓存;
    step9:删除二级分区并重建索引;
    step9.1:查询表;
    step9.2:查看gs_gsc_dbstat_info函数gsc缓存;
    step10:删除一级分区并重建索引;
    step10.1:查询表;
    step10.2:查看gs_gsc_dbstat_info函数gsc缓存;
    step11:增加字段;
    step11.1:查询表;
    step11.2:查看gs_gsc_dbstat_info函数gsc缓存;
    step12:删除字段;
    step12.1:查询表;
    step12.2:查看gs_gsc_dbstat_info函数gsc缓存;
    step13:删除索引;
    step13.1:查询表;
    step13.2:查看gs_gsc_dbstat_info函数gsc缓存;
    step14:删除表;
    step14.1:查看syscache占用情况;
Expect      :
    step1:打开gsc开关; expect:修改成功
    step2:重启数据库生效; expect:重启成功
    step3:创建数据库; expect:创建成功
    step4:创建表插入数据，并查询; expect:操作成功
    step5:查询表; expect:操作成功
    step5.1:查看gs_gsc_dbstat_info函数gsc缓存; expect:tup/rel/part缓存内存均大于0
    step6:增加索引; expect:操作成功
    step6.1:查询表; expect:操作成功
    step6.2:查看gs_gsc_dbstat_info函数gsc缓存; expect：tup/rel缓存count信息较步骤5.1增大;
        part缓存count信息较步骤5不变
    step7:修改分区表，添加二级分区; expect:操作成功
    step7.1:查询表; expect:操作成功
    step7.2:查看gs_gsc_dbstat_info函数gsc缓存; expect:tup/rel缓存count信息较步骤6.2增大或者不变;
        part缓存count信息较步骤6.2不变
    step8:修改分区表，添加一级分区; expect:操作成功
    step8.1:查询表; expect:操作成功
    step8.2:查看gs_gsc_dbstat_info函数gsc缓存;
        expect:tup/rel缓存count信息较步骤7.2增大或者不变;part缓存count信息较步骤7.2增大
    step9:删除二级分区并重建索引; expect:操作成功
    step9.1:查询表; expect:操作成功
    step9.2:查看gs_gsc_dbstat_info函数gsc缓存; expect:part_count/memory较步骤8.2减少
    step10:删除一级分区并重建索引; expect:操作成功
    step10.1:查询表; expect:操作成功
    step10.2:查看gs_gsc_dbstat_info函数gsc缓存; expect:part_count/memory较步骤9.2减少
    step11:增加字段; expect:操作成功
    step11.1:查询表; expect:操作成功
    step11.2:查看gs_gsc_dbstat_info函数gsc缓存; expect:tup缓存count信息较步骤10.2增大;
        rel缓存信息较步骤10.2不变part缓存count信息较步骤10.2不变
    step12:删除字段; expect:操作成功
    step12.1:查询表; expect:操作成功
    step12.2:查看gs_gsc_dbstat_info函数gsc缓存;
        expect:part缓存count信息较步骤11.2不变；tup缓存dead信息为0
    step13:删除索引; expect:操作成功
    step13.1:查询表; expect:操作成功
    step13.2:查看gs_gsc_dbstat_info函数gsc缓存;
        expect: tup、rel缓存dead信息为0、count减少;part缓存count信息较步骤12.2不变
    step14:删除表; expect:操作成功
    step14.1:查看syscache占用情况;
        expect:tup/rel/part缓存dead信息为0,tup缓存count信息减少;part缓存count信息清零
History     :
    Modify by peilinqian at 2024-6-20:rel缓存信息较步骤10.2不变，优化用例
    Modify by peilinqian at 2024-8-8:研发代码变更，refcount为0的情况下，dead缓存为0，直接清理
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node

pri_sh = CommonSH('PrimaryDbUser')


class GscTestCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.log.info("----this is setup----")
        self.pri_dbuser = Node(node='PrimaryDbUser')
        self.pri_root = Node(node='PrimaryRoot')
        self.db_name = 'db_gsc0074'
        self.tb_name = "tb_gsc0074"
        self.index_name = "index_gsc0074"
        self.com = Common()
        self.constant = Constant()

    def test_main(self):
        step_txt = '----step0:查看数据库状态是否正常;----'
        self.log.info(step_txt)
        status_result = pri_sh.get_db_cluster_status('status')
        self.assertTrue(status_result, '执行失败:' + step_txt)
        step_txt = '----step0:查看enable_global_syscache初始配置值;----'
        self.log.info(step_txt)
        self.init_para1 = self.com.show_param("enable_global_syscache")

        if self.init_para1 == 'off':
            step_txt = '----step1:打开gsc开关; expect:修改成功----'
            self.log.info(step_txt)
            result = pri_sh.execute_gsguc('set',
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f"enable_global_syscache= on")
            self.assertTrue(result, '执行失败:' + step_txt)

            step_txt = '----step2:重启数据库生效; expect:重启成功----'
            self.log.info(step_txt)
            restart = pri_sh.restart_db_cluster()
            self.assertTrue(restart, '执行失败:' + step_txt)
            self.is_reset_para = True
        else:
            step_txt = '----gsc开关设置正常，跳过step1、step2----'
            self.log.info(step_txt)
            self.is_reset_para = False

        step_txt = '----step3:创建数据库; expect:创建成功----'
        self.log.info(step_txt)
        create_db = f'drop database if exists {self.db_name};' \
            f'create database {self.db_name};'
        create_result = pri_sh.execut_db_sql(create_db)
        self.log.info(create_result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, create_result,
                      '执行失败:' + step_txt)

        step_txt = '----step4:创建表，并插入数据; expect:操作成功----'
        self.log.info(step_txt)
        self.create_sql = f"drop table if exists {self.tb_name};" \
            f"create table {self.tb_name}(jid int,jn int ,name int)" \
            f"partition by range (jid) subpartition by range(jn)(" \
            f"partition hr1 values less than(10000)(" \
            f"subpartition hr11 values less than(10000)," \
            f"subpartition hr12 values less than(30000))," \
            f"partition hr2 values less than(30000)(" \
            f"subpartition hr21 values less than(10000)," \
            f"subpartition hr22 values less than(maxvalue)));" \
            f"select * from {self.tb_name};" \
            f"insert into {self.tb_name} select generate_series(1,20000)," \
            f"generate_series(1,20000), " \
            f"generate_series(1,20000);"
        result = pri_sh.execut_db_sql(self.create_sql,
                                      dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, result,
                      "执行失败" + step_txt)
        self.assertIn('0 rows', result, "执行失败" + step_txt)

        step_txt = '----step5:查询表; expect:操作成功----'
        self.log.info(step_txt)
        select_sql = f"select * from {self.tb_name} " \
            f"where name > 400 limit 1;"
        result = pri_sh.execut_db_sql(select_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        step_txt = '----step5.1:查看gs_gsc_dbstat_info函数gsc缓存; ' \
                   'expect:tup/rel/part缓存内存均大于0----'
        self.log.info(step_txt)
        check_sql = f"select * from gs_gsc_dbstat_info() " \
            f"where database_name = '{self.db_name}';"
        select_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(select_result)
        step5_1_result = self.com.parse_sql_res_with_col_name(select_result,
                                                              False)
        self.assertGreater(int(step5_1_result[0]['tup_memory']), 0,
                           '执行失败:' + step_txt)
        self.assertGreater(int(step5_1_result[0]['rel_memory']), 0,
                           '执行失败:' + step_txt)
        self.assertGreater(int(step5_1_result[0]['part_memory']), 0,
                           '执行失败:' + step_txt)

        step_txt = '----step6:增加索引; expect:操作成功----'
        self.log.info(step_txt)
        create_sql = f"create index {self.index_name} on " \
            f"{self.tb_name}(name);"
        result = pri_sh.execut_db_sql(create_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS, result,
                      "执行失败" + step_txt)
        step_txt = '----step6.1:查询表; expect:操作成功----'
        self.log.info(step_txt)
        result = pri_sh.execut_db_sql(select_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        step_txt = '----step6.2:查看gs_gsc_dbstat_info函数gsc缓存; ' \
                   'expect：tup/rel缓存count信息较步骤5.1增大;' \
                   'part缓存count信息较步骤5不变----'
        self.log.info(step_txt)
        select_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(select_result)
        step6_2_result = self.com.parse_sql_res_with_col_name(select_result,
                                                              False)
        self.assertGreater(int(step6_2_result[0]['tup_count']),
                           int(step5_1_result[0]['tup_count']),
                           '执行失败:' + step_txt)
        self.assertGreater(int(step6_2_result[0]['rel_count']),
                           int(step5_1_result[0]['rel_count']),
                           '执行失败:' + step_txt)
        self.assertEqual(int(step6_2_result[0]['part_count']),
                         int(step5_1_result[0]['part_count']),
                         '执行失败:' + step_txt)

        step_txt = '----step7:修改分区表，添加二级分区; expect:操作成功----'
        self.log.info(step_txt)
        alter_sql = f"alter table {self.tb_name} modify partition " \
            f"hr1 add subpartition hr13 values less than(50000);"
        result = pri_sh.execut_db_sql(alter_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result,
                      "执行失败" + step_txt)
        step_txt = '----step7.1:查询表; expect:操作成功----'
        self.log.info(step_txt)
        result = pri_sh.execut_db_sql(select_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        step_txt = '----step7.2:查看gs_gsc_dbstat_info函数gsc缓存; ' \
                   'expect:tup/rel缓存count信息较步骤6.2增大或者不变;' \
                   'part缓存count信息较步骤6.2不变----'
        self.log.info(step_txt)
        select_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(select_result)
        step7_2_result = self.com.parse_sql_res_with_col_name(select_result,
                                                              False)
        self.assertGreaterEqual(int(step7_2_result[0]['tup_count']),
                                int(step6_2_result[0]['tup_count']),
                                '执行失败:' + step_txt)
        self.assertGreaterEqual(int(step7_2_result[0]['rel_count']),
                                int(step6_2_result[0]['rel_count']),
                                '执行失败:' + step_txt)
        self.assertGreater(int(step7_2_result[0]['part_count']),
                           int(step6_2_result[0]['part_count']),
                           '执行失败:' + step_txt)

        step_txt = '----step8:修改分区表，添加一级分区; expect:操作成功----'
        self.log.info(step_txt)
        alter_sql = f"alter table {self.tb_name} add partition hr3 " \
            f"values less than(50000);"
        result = pri_sh.execut_db_sql(alter_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result,
                      "执行失败" + step_txt)
        step_txt = '----step8.1:查询表; expect:操作成功----'
        self.log.info(step_txt)
        result = pri_sh.execut_db_sql(select_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        step_txt = '----step8.2:查看gs_gsc_dbstat_info函数gsc缓存; ' \
                   'expect:tup/rel缓存count信息较步骤7.2增大或者不变;' \
                   'part缓存count信息较步骤7.2增大----'
        self.log.info(step_txt)
        select_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(select_result)
        step8_2_result = self.com.parse_sql_res_with_col_name(select_result,
                                                              False)
        self.assertGreaterEqual(int(step8_2_result[0]['tup_count']),
                                int(step7_2_result[0]['tup_count']),
                                '执行失败:' + step_txt)
        self.assertGreaterEqual(int(step8_2_result[0]['rel_count']),
                                int(step7_2_result[0]['rel_count']),
                                '执行失败:' + step_txt)
        self.assertGreater(int(step8_2_result[0]['part_count']),
                           int(step7_2_result[0]['part_count']),
                           '执行失败:' + step_txt)

        step_txt = '----step9:删除二级分区并重建索引; expect:操作成功----'
        self.log.info(step_txt)
        drop_sql = f"alter table {self.tb_name} drop subpartition hr13;" \
            f"reindex index {self.index_name};"
        result = pri_sh.execut_db_sql(drop_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result,
                      "执行失败" + step_txt)
        self.assertIn(self.constant.REINDEX_SUCCESS_MSG, result,
                      "执行失败" + step_txt)
        step_txt = '----step9.1:查询表; expect:操作成功----'
        self.log.info(step_txt)
        result = pri_sh.execut_db_sql(select_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        step_txt = '----step9.2:查看gs_gsc_dbstat_info函数gsc缓存; ' \
                   'expect:part_count/memory较步骤8.2减少----'
        self.log.info(step_txt)
        select_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(select_result)
        step9_2_result = self.com.parse_sql_res_with_col_name(select_result,
                                                              False)
        self.assertLess(int(step9_2_result[0]['part_count']),
                        int(step8_2_result[0]['part_count']),
                        '执行失败:' + step_txt)
        self.assertLess(int(step9_2_result[0]['part_memory']),
                        int(step8_2_result[0]['part_memory']),
                        '执行失败:' + step_txt)

        step_txt = '----step10:删除一级分区并重建索引; expect:操作成功----'
        self.log.info(step_txt)
        drop_sql = f"alter table {self.tb_name} drop partition hr3 ;" \
            f"reindex index {self.index_name};"
        result = pri_sh.execut_db_sql(drop_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result,
                      "执行失败" + step_txt)
        self.assertIn(self.constant.REINDEX_SUCCESS_MSG, result,
                      "执行失败" + step_txt)
        step_txt = '----step10.1:查询表; expect:操作成功----'
        self.log.info(step_txt)
        result = pri_sh.execut_db_sql(select_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        step_txt = '----step10.2:查看gs_gsc_dbstat_info函数gsc缓存; ' \
                   'expect:part_count/memory较步骤9.2减少----'
        self.log.info(step_txt)
        select_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(select_result)
        step10_2_result = self.com.parse_sql_res_with_col_name(select_result,
                                                               False)
        self.assertLess(int(step10_2_result[0]['part_count']),
                        int(step9_2_result[0]['part_count']),
                        '执行失败:' + step_txt)
        self.assertLess(int(step10_2_result[0]['part_memory']),
                        int(step9_2_result[0]['part_memory']),
                        '执行失败:' + step_txt)

        step_txt = '----step11:增加字段; expect:操作成功----'
        self.log.info(step_txt)
        alter_sql = f"alter table {self.tb_name} add " \
            f"(bak1 varchar(100),bak2 char);"
        result = pri_sh.execut_db_sql(alter_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result,
                      "执行失败" + step_txt)
        step_txt = '----step11.1:查询表; expect:操作成功----'
        self.log.info(step_txt)
        result = pri_sh.execut_db_sql(select_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        step_txt = '----step11.2:查看gs_gsc_dbstat_info函数gsc缓存; ' \
                   'expect:tup缓存count信息较步骤10.2增大;rel缓存信息较步骤10.2不变' \
                   'part缓存count信息较步骤10.2不变----'
        self.log.info(step_txt)
        select_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(select_result)
        step11_2_result = self.com.parse_sql_res_with_col_name(select_result,
                                                               False)
        self.assertGreater(int(step11_2_result[0]['tup_count']),
                           int(step10_2_result[0]['tup_count']),
                           '执行失败:' + step_txt)
        self.assertEqual(int(step11_2_result[0]['rel_count']),
                         int(step10_2_result[0]['rel_count']),
                         '执行失败:' + step_txt)
        self.assertEqual(int(step11_2_result[0]['part_count']),
                         int(step10_2_result[0]['part_count']),
                         '执行失败:' + step_txt)

        step_txt = '----step12:删除字段; expect:操作成功----'
        self.log.info(step_txt)
        drop_sql = f"alter table {self.tb_name} drop column bak1;"
        result = pri_sh.execut_db_sql(drop_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result,
                      "执行失败" + step_txt)
        step_txt = '----step12.1:查询表; expect:操作成功----'
        self.log.info(step_txt)
        result = pri_sh.execut_db_sql(select_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        step_txt = '----step12.2:查看gs_gsc_dbstat_info函数gsc缓存; ' \
                   'expect:part缓存count信息较步骤11.2不变；' \
                   'tup缓存dead信息为0----'
        self.log.info(step_txt)
        select_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(select_result)
        step12_2_result = self.com.parse_sql_res_with_col_name(select_result,
                                                               False)
        self.assertEqual(int(step12_2_result[0]['part_count']),
                         int(step11_2_result[0]['part_count']),
                         '执行失败:' + step_txt)
        self.assertEqual(int(step12_2_result[0]['tup_dead']), 0,
                         '执行失败:' + step_txt)

        step_txt = '----step13:删除索引; expect:操作成功----'
        self.log.info(step_txt)
        drop_sql = f"drop index {self.index_name};"
        result = pri_sh.execut_db_sql(drop_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('DROP INDEX', result, "执行失败" + step_txt)
        step_txt = '----step13.1:查询表; expect:操作成功----'
        self.log.info(step_txt)
        result = pri_sh.execut_db_sql(select_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn('1 row', result, "执行失败" + step_txt)
        step_txt = '----step13.2:查看gs_gsc_dbstat_info函数gsc缓存; ' \
                   'expect: tup、rel缓存dead信息为0、count减少;' \
                   'part缓存count信息较步骤12.2不变----'
        self.log.info(step_txt)
        select_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(select_result)
        step13_2_result = self.com.parse_sql_res_with_col_name(select_result,
                                                               False)
        self.assertEqual(int(step13_2_result[0]['tup_dead']), 0,
                         '执行失败:' + step_txt)
        self.assertLess(int(step13_2_result[0]['tup_count']),
                        int(step12_2_result[0]['tup_count']),
                        '执行失败:' + step_txt)
        self.assertLess(int(step13_2_result[0]['rel_count']),
                        int(step12_2_result[0]['rel_count']),
                        '执行失败:' + step_txt)
        self.assertEqual(int(step13_2_result[0]['part_count']),
                         int(step12_2_result[0]['part_count']),
                         '执行失败:' + step_txt)

        step_txt = '----step14:删除表; expect:操作成功----'
        self.log.info(step_txt)
        drop_sql = f"drop table if exists {self.tb_name};"
        result = pri_sh.execut_db_sql(drop_sql, dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, result,
                      "执行失败" + step_txt)
        step_txt = '----step14.1:查看syscache占用情况; ' \
                   'expect:tup/rel/part缓存dead信息为0,tup缓存count信息减少;' \
                   'part缓存count信息清零----'
        self.log.info(step_txt)
        select_result = pri_sh.execut_db_sql(check_sql)
        self.log.info(select_result)
        step14_1_result = self.com.parse_sql_res_with_col_name(select_result,
                                                               False)
        self.assertEqual(int(step14_1_result[0]['tup_dead']), 0,
                         '执行失败:' + step_txt)
        self.assertLess(int(step14_1_result[0]['tup_count']),
                        int(step13_2_result[0]['tup_count']),
                        '执行失败:' + step_txt)
        self.assertEqual(int(step14_1_result[0]['rel_dead']), 0,
                         '执行失败:' + step_txt)
        self.assertEqual(int(step14_1_result[0]['part_count']), 0,
                         '执行失败:' + step_txt)
        self.assertEqual(int(step14_1_result[0]['part_dead']), 0,
                         '执行失败:' + step_txt)

    def tearDown(self):
        self.log.info('----this is tearDown----')
        step1_txt = '----清理数据库; expect:清理成功----'
        self.log.info(step1_txt)
        drop_cmd = f"drop database if exists {self.db_name};"
        drop_result = pri_sh.execut_db_sql(drop_cmd)
        self.log.info(drop_result)

        if self.is_reset_para:
            self.step2_txt = '----恢复参数为初始值并查询; expect:设置成功----'
            self.log.info(self.step2_txt)
            self.msg1 = pri_sh.execute_gsguc(
                'set', self.constant.GSGUC_SUCCESS_MSG,
                f"enable_global_syscache ={self.init_para1}")
            self.step3_txt = '----重启数据库生效; expect:重启成功----'
            self.log.info(self.step3_txt)
            self.restart = pri_sh.restart_db_cluster()
            self.step4_txt = '----查询数据库状态; expect:状态正常----'
            self.log.info(self.step4_txt)
            self.status_result = pri_sh.get_db_cluster_status('status')

        step_txt = '----teardown断言----'
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_result,
                      "执行失败" + step_txt + step1_txt)
        if self.is_reset_para:
            self.assertTrue(self.msg1, '执行失败:' + self.step2_txt)
            self.assertTrue(self.restart, '执行失败:' + self.step3_txt)
            self.assertTrue(self.status_result, '执行失败:' + self.step4_txt)

        self.log.info(f'----{os.path.basename(__file__)}:end----')
